// 测试接口 参考


const express = require('express');
const user = express.Router();
// 引入mySqlDb模块
const {
	execSQL
} = require('../db/mySqlDb');
const table = 'my_text';

const bodyParser = require('body-parser');
// 使用body-parser中间件解析请求体数据
user.use(bodyParser.json());
user.use(bodyParser.urlencoded({
	extended: false
}));
// connection.end();

// （获取+搜索+分页）用户列表接口   多条件查询
user.get('/list', (req, res, next) => {
	// 获取前端的参数
	let {
		name,
		age,
		page,
		size
	} = req.query;
	page = (page - 1) * size;

	let conditions = [];
	let values = [];

	if (name) {
		conditions.push(`user_name like '%${name}%'`);
		values.push(`%${name}%`);
	}

	if (age) {
		conditions.push(`user_age = '${age}'`);
		values.push(age);
	}

	let whereClause = "";
	if (conditions.length > 0) {
		whereClause = "where " + conditions.join(" and ");
	}
	// 查询列表sql
	const pageSql = `select * from ${table} ${whereClause} order by user_id limit ${page},${size}`;
	// 查询总数的sql
	const totalSql = `select count(*) as total from ${table} ${whereClause}`;
	execSQL(pageSql).then(_result => {
		execSQL(totalSql, values).then(count => {
			const totalRecords = count[0].total; // 数据库中的总记录数
			const pages = Math.ceil(totalRecords / size); // 计算总页数
			res.send({
				code: 200,
				msg: '操作成功',
				data: {
					data: _result,
					total: count[0].total,
					pages
				}
			});
		}).catch(err => {
			return res.send({
				code: 500,
				msg: '数据库总数查询失败'
			})
		})
	}).catch(err => {
		return res.send({
			code: 500,
			msg: '数据库列表查询失败'
		})
	})

});


user.get('/detail', (req, res, next) => {

	res.send({
		name: '可乐',
		price: 3,
		count: 100
	});
})
module.exports = user;